package mww.javabean.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import mww.common.ConnectionFactory;
import mww.common.DaoException;
import mww.common.Page;
import mww.common.ResourceClose;
import mww.javabean.entity.TeacherC;

public class TeacherCDaoImpl implements TeacherCDao {

	@Override
	public void addTeacherC(TeacherC tc) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();// 调用ConnectionFactory组件的getConnection()方法来获得数据库连接
			String sql = "insert into TeacherC(teacherNo, teacherName, CourseNo, CourseName,Credit, TcTeacherNo,TcLocation,TcTime,TcCapacity,TcSel,Status) values(?,?,?,?,?,?,?,?,?,0,0)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tc.getTeacherNo());
			pstmt.setString(2, tc.getTeacherName());
			pstmt.setString(3, tc.getCourseNo());
			pstmt.setString(4, tc.getCourseName());
			pstmt.setFloat(5, tc.getCredit());
			pstmt.setInt(6, tc.getTcTeacherNo());
			pstmt.setString(7, tc.getTcLocation());
			pstmt.setString(8, tc.getTcTime());
			pstmt.setInt(9, tc.getTcCapacity());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);// 调用ResourceClose组件的close方法来关闭数据库资源
		}
	}

	@Override
	public void delTeacherC(String teacherNo, String courseNo, Integer teTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update TeacherC set status=1 WHERE teacherNo='" + teacherNo + "' and courseNo='" + courseNo
					+ "' and tcTeacherNo=" + teTeacherNo + ";";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public Map findTeacherCByTeacherNo(String teacherNo, Integer status, Integer curPage) {
		TeacherC tc = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示对象
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		ResultSet r = null;
		Map map = null;
		Page pa = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from TeacherC where teacherNo = '" + teacherNo + "' and status=" + 0
					+ " order by teacherNo;";
			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);// 设置每页所要显示记录个数
			pa.setPageCount(rs);// 根据结果集设置所有要显示的记录总数
			pa.setCurPage(curPage);// 设置所要显示页
			r = pa.setRs(rs);// 设置要显示的结果集，此时记录指针处于第一条记录上
			r.previous();// 记录指针前移，使之处于第一条记录之前的位置
			// 利用循环操作将查询到的结果集放入List对象中
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (r.next()) {
					tc = new TeacherC();
					tc.setTeacherNo(rs.getString("teacherNo"));
					tc.setCourseNo(rs.getString("courseNo"));
					tc.setCourseName(rs.getString("courseName"));
					tc.setCredit(rs.getFloat("credit"));
					tc.setTcLocation(rs.getString("TcLocation"));
					tc.setTcTeacherNo(rs.getInt("TcTeacherNo"));
					tc.setTcTime(rs.getString("TcTime"));
					tc.setTcCapacity(rs.getInt("TcCapacity"));
					tc.setTcSel(rs.getInt("TcSel"));
					list.add(tc);
				} else {
					break;
				}
			}
			map = new HashMap();// 声明一个Map对象
			map.put("list", list);// 将list对象放入Map对象
			map.put("pa", pa);// 将Page对象放入Map对象
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	@Override
	public String findTeacherNo(String courseNo, Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String teacherNo = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT teacherNo from teacherC where courseNo='" + courseNo + "' and TcTeacherNo="
					+ tcTeacherNo + " and status=0;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				teacherNo = rs.getString("teacherNo");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return teacherNo;
	}

	@Override
	public String findTcTime(String teacherNo, String courseNo, Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String tcTime = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT teacherNo from TeacherC where teacherNo='" + teacherNo + "' courseNo='" + courseNo
					+ "' and TcTeacherNo=" + tcTeacherNo + " and status=0;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				tcTime = rs.getString("TcTime");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return tcTime;
	}

	@Override
	public void incTcSel(String teacherNo, String courseNo, Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE teacherC set TcSel=TcSel+1 WHERE teacherNo='" + teacherNo + "' and courseNo='"
					+ courseNo + "' and tcTeacherNo=" + tcTeacherNo + " and status=0;";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public void decTcSel(String teacherNo, String courseNo, Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE teacherC set TcSel=TcSel-1 WHERE teacherNo='" + teacherNo + "' and courseNo='"
					+ courseNo + "' and tcTeacherNo=" + tcTeacherNo + " and status=0;";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public Integer findStatus(String teacherNo, String courseNo, Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Integer status = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT status from TeacherC where courseNo='" + courseNo + "' and TeacherNo=" + teacherNo
					+ " and tcTeacherNo=" + tcTeacherNo + " ;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				status = rs.getInt("status");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return status;
	}

	@Override
	public void updateStatus(String teacherNo, String courseNo,Integer tcTeacherNo, Integer status) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE TeacherC set status=" + status + " WHERE teacherNo='" + teacherNo + "' and courseNo='"
					+ courseNo + "' and tcTeacherNo="+tcTeacherNo+" ;";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public Map findAllTeacherCByMostCon(TeacherC condition, int curPage) {
		TeacherC tc = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示学生对象
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		ResultSet r = null;
		Map map = null;
		Page pa = null;
		try {
			conn = ConnectionFactory.getConnection();

			String courseNo = condition.getCourseNo();
			String courseName = condition.getCourseName();
			Float credit = condition.getCredit();
			Integer tcTeacherNo = condition.getTcTeacherNo();
			String teacherName = condition.getTeacherName();
			String tcTime = condition.getTcTime();

			String sql = "SELECT * FROM teacherC where 1=1 ";

			if (courseNo != null && !courseNo.equals("")) {
				sql += " and courseNo like '%" + courseNo + "%'";
			}
			if (courseName != null && !courseName.equals("")) {
				sql += " and courseName like '%" + courseName + "%'";
			}
			if (credit != null && 0 != credit) {
				sql += " and credit=" + credit;
			}
			if (teacherName != null && !teacherName.equals("")) {
				sql += " and teacherName like'%" + teacherName + "%'";
			}
			if (tcTeacherNo != null && 0 != tcTeacherNo) {
				sql += " and tcTeacherNo like'%" + tcTeacherNo + "%'";
			}
			if (tcTime != null && !tcTime.equals("")) {
				sql += " and tcTime like '%" + tcTime + "%'";
			}
			sql += " and status=0 order by courseNo";

			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);// 设置每页所要显示记录个数
			pa.setPageCount(rs);// 根据结果集设置所有要显示的记录总数
			pa.setCurPage(curPage);// 设置所要显示页
			r = pa.setRs(rs);// 设置要显示的结果集，此时记录指针处于第一条记录上
			r.previous();// 记录指针前移，使之处于第一条记录之前的位置
			// 利用循环操作将查询到的结果集放入List对象中
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (r.next()) {
					tc = new TeacherC();
					tc.setCourseNo(rs.getString("courseNo"));
					tc.setCourseName(rs.getString("courseName"));
					tc.setTeacherNo(rs.getString("teacherNo"));
					tc.setTcTeacherNo(rs.getInt("tcTeacherNo"));
					tc.setCredit(rs.getFloat("credit"));
					tc.setTcLocation(rs.getString("tcLocation"));
					tc.setTcTime(rs.getString("tcTime"));
					tc.setTcCapacity(rs.getInt("tcCapacity"));
					tc.setTcSel(rs.getInt("tcSel"));
					tc.setTeacherName(rs.getString("teacherName"));
					list.add(tc);
				} else {
					break;
				}
			}
			map = new HashMap();// 声明一个Map对象
			map.put("list", list);// 将list对象放入Map对象
			map.put("pa", pa);// 将Page对象放入Map对象
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	@Override
	public TeacherC findTeacherCById(String teacherNo, String courseNo,Integer tcTeacherNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		TeacherC tc = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from TeacherC  WHERE teacherNo='"+teacherNo+"' and tcTeacherNo=" + tcTeacherNo + " and courseNo='" + courseNo+"' ;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				tc = new TeacherC();
				tc.setTeacherNo(rs.getString("teacherNo"));
				tc.setTeacherName(rs.getString("teacherName"));
				tc.setCourseNo(rs.getString("courseNo"));
				tc.setCourseName(rs.getString("courseName"));
				tc.setCredit(rs.getFloat("credit"));
				tc.setAvgScore(rs.getFloat("avgScore"));
				tc.setMaxScore(rs.getFloat("maxScore"));
				tc.setMinScore(rs.getFloat("minScore"));
				tc.setTcLocation(rs.getString("TcLocation"));
				tc.setTcTeacherNo(rs.getInt("TcTeacherNo"));
				tc.setTcTime(rs.getString("TcTime"));
				tc.setTcCapacity(rs.getInt("TcCapacity"));
				tc.setTcSel(rs.getInt("TcSel"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return tc;
	}

	@Override
	public Map findAllTeacherC(int curPage) {
		TeacherC tc = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示学生对象
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		ResultSet r = null;
		Map map = null;
		Page pa = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from TeacherC where status=0 order by courseNo";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);// 设置每页所要显示记录个数
			pa.setPageCount(rs);// 根据结果集设置所有要显示的记录总数
			pa.setCurPage(curPage);// 设置所要显示页
			r = pa.setRs(rs);// 设置要显示的结果集，此时记录指针处于第一条记录上
			r.previous();// 记录指针前移，使之处于第一条记录之前的位置
			// 利用循环操作将查询到的结果集放入List对象中
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (r.next()) {
					tc = new TeacherC();
					tc.setCourseNo(rs.getString("courseNo"));
					tc.setCourseName(rs.getString("courseName"));
					tc.setTcTeacherNo(rs.getInt("tcTeacherNo"));
					tc.setTeacherNo(rs.getString("teacherNo"));
					tc.setCredit(rs.getFloat("credit"));
					tc.setTcLocation(rs.getString("tcLocation"));
					tc.setTcTime(rs.getString("tcTime"));
					tc.setTcCapacity(rs.getInt("tcCapacity"));
					tc.setTcSel(rs.getInt("tcSel"));
					tc.setTeacherName(rs.getString("teacherName"));
					list.add(tc);
				} else {
					break;
				}
			}
			map = new HashMap();// 声明一个Map对象
			map.put("list", list);// 将list对象放入Map对象
			map.put("pa", pa);// 将Page对象放入Map对象
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	@Override
	public void updateScoreInTeacherC(String teacherNo, String courseNo,Integer tcTeacherNo, float maxScore, float minScore,
			float avgScore) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update teacherC set maxScore=" + maxScore + ",minScore=" + minScore + ",avgScore=" + avgScore
					+ " WHERE teacherNo='" + teacherNo + "' and courseNo='" + courseNo + "' and tcTeacherNo="+tcTeacherNo+" and status=0;";
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}

	}

	@Override
	public boolean checkTeacherCExistByCourseNo(String courseNo) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from teacherC where courseNo=" + courseNo + " and status=0; ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return flag;
	}

	@Override
	public boolean checkTeacherCExistByTeacherNo(String teacherNo) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from teacherC where teacherNo=? and status=0";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherNo);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return flag;
	}

	@Override
	public boolean checkExistConflict(String teacherNo, String courseNo,Integer tcTeacherNo) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from TeacherC  WHERE teacherNo='" + teacherNo + "' and courseNo='" + courseNo
					+ "' and tcTeacherNo="+tcTeacherNo+" and status=0;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return flag;
	}

	@Override
	public boolean checkTimeConflict(String teacherNo, String tcTime) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from TeacherC  WHERE teacherNo='" + teacherNo + "' and tcTime='" + tcTime
					+ "' and status=0 ;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return flag;
	}

}
